package com.jcp.daoimp;


import java.util.ArrayList;


import java.text.SimpleDateFormat;
import java.util.Date;

import java.util.List;

import com.jcp.dao.AttencesDao;
import com.jcp.entity.Attences;

import com.jcp.entity.CountClock;

import com.jcp.entity.OverTime;
import com.jcp.util.DBUtil;
import com.jcp.util.PageData;

public class AttencesDaoImp implements AttencesDao {


	/**
	 * 查看考勤有关的dao
	 * 
	 * @author 朱徐烽
	 *
	 */
	public PageData<Attences> selAttenceByUserIdByPage(int page, int pageSize, int userId) {

		String sql ="select * from attences where  userId=? order by attTime desc";
		//List<Attences> list = DBUtil.exQuery(sql, Attences.class, userId);
		PageData<Attences> pageData=DBUtil.exQueryByPage(sql, Attences.class, page, pageSize, userId);
		return pageData;
	}
	
	
	@Override
	public List<Attences> WeekSel1() {
		// TODO Auto-generated method stub
		//上周的迟到人数
		String sql1 = "SELECT count(*) as attId from attences  where YEARWEEK(DATE_FORMAT(attTime,'%y-%m-%d'))= YEARWEEK(NOW())-1 and attFlag=1";
		List<Attences> list1 = DBUtil.exQuery(sql1, Attences.class);
		
		return list1;
	}
	@Override
	public List<Attences> WeekSel2() {
		// TODO Auto-generated method stub
		//上周的早退人数
		String sql2="SELECT count(*) as attId from attences  where YEARWEEK(DATE_FORMAT(attTime,'%y-%m-%d'))= YEARWEEK(NOW())-1 and attFlag=2";
		List<Attences> list2 = DBUtil.exQuery(sql2, Attences.class);
		
		return list2;
	}
	@Override
	public List<Attences> WeekSel3() {
		// TODO Auto-generated method stub
		//上周的加班人数
		String sql3="SELECT count(*) as attId from attences  where YEARWEEK(DATE_FORMAT(attTime,'%y-%m-%d'))= YEARWEEK(NOW())-1 and attFlag=3";
		List<Attences> list3 = DBUtil.exQuery(sql3, Attences.class);
		return list3;
	}

	@Override
	public List<Attences> WeekSel0() {
		String sql4="SELECT count(*) as attId from attences  where YEARWEEK(DATE_FORMAT(attTime,'%y-%m-%d'))= YEARWEEK(NOW())-1 and attFlag=0";
		List<Attences> list4 = DBUtil.exQuery(sql4, Attences.class);
		return list4;
	}

	@Override
	public List<Attences> Weekchidao1() {
		// TODO Auto-generated method stub
		//查询上周一到上周五的迟到人数
		String sql1 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+6)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attences.attFlag=1";
		String sql2 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attences.attFlag=1";
		String sql3 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attences.attFlag=1";
		String sql4 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attences.attFlag=1";
		String sql5 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+1)) and attences.attFlag=1";
		
		List<Attences> list1 =  DBUtil.exQuery(sql1,Attences.class);
		int n1 =list1.get(0).getAttId();
		List<Attences> list2 =  DBUtil.exQuery(sql2,Attences.class);
		int n2 =list2.get(0).getAttId();
		List<Attences> list3 =  DBUtil.exQuery(sql3,Attences.class);
		int n3 =list3.get(0).getAttId();
		List<Attences> list4 =  DBUtil.exQuery(sql4,Attences.class);
		int n4 =list4.get(0).getAttId();
		List<Attences> list5 =  DBUtil.exQuery(sql5,Attences.class);
		int n5 =list5.get(0).getAttId();
		List list = new ArrayList();
		list.add(n1);
		list.add(n2);
		list.add(n3);
		list.add(n4);
		list.add(n5);
		
		return list;
	}

	@Override
	/**
	 * 查询加班时长前三名
	 * 
	 * @return List<OverTime>
	 */
	public List<OverTime> selOverTime() {
		// TODO Auto-generated method stub
		String sql = "SELECT attences.userId,users.userName,SUM(attences.overTime) as overTime,departments.deptName,files.photosurl FROM attences INNER JOIN users on users.userId=attences.userId inner join departments on users.deptId=departments.deptId inner join files on files.cardId=users.cardId GROUP BY userId ORDER BY overTime DESC LIMIT 0,3";
		List<OverTime> list = DBUtil.exQuery(sql, OverTime.class);


		return list;
	}




	@Override
	/**
	 * 查询上周一到上周五的迟到人数
	 */
	public List<Attences> Weekzaotui1() {
		// TODO Auto-generated method stub
		String sql1 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+6)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attences.attFlag=2";
		String sql2 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attences.attFlag=2";
		String sql3 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attences.attFlag=2";
		String sql4 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attences.attFlag=2";
		String sql5 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+1)) and attences.attFlag=2";
		List<Attences> list1 =  DBUtil.exQuery(sql1,Attences.class);
		int n1 =list1.get(0).getAttId();
		List<Attences> list2 =  DBUtil.exQuery(sql2,Attences.class);
		int n2 =list2.get(0).getAttId();
		List<Attences> list3 =  DBUtil.exQuery(sql3,Attences.class);
		int n3 =list3.get(0).getAttId();
		List<Attences> list4 =  DBUtil.exQuery(sql4,Attences.class);
		int n4 =list4.get(0).getAttId();
		List<Attences> list5 =  DBUtil.exQuery(sql5,Attences.class);
		int n5 =list5.get(0).getAttId();
		List list = new ArrayList();
		list.add(n1);
		list.add(n2);
		list.add(n3);
		list.add(n4);
		list.add(n5);
		return list;
	}

	@Override
	public List<Attences> Weekjiaban1() {
		// TODO Auto-generated method stub
		String sql1 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+6)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attences.attFlag=3";
		String sql2 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attences.attFlag=3";
		String sql3 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attences.attFlag=3";
		String sql4 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attences.attFlag=3";
		String sql5 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+1)) and attences.attFlag=3";
		List<Attences> list1 =  DBUtil.exQuery(sql1,Attences.class);
		int n1 =list1.get(0).getAttId();
		List<Attences> list2 =  DBUtil.exQuery(sql2,Attences.class);
		int n2 =list2.get(0).getAttId();
		List<Attences> list3 =  DBUtil.exQuery(sql3,Attences.class);
		int n3 =list3.get(0).getAttId();
		List<Attences> list4 =  DBUtil.exQuery(sql4,Attences.class);
		int n4 =list4.get(0).getAttId();
		List<Attences> list5 =  DBUtil.exQuery(sql5,Attences.class);
		int n5 =list5.get(0).getAttId();
		List list = new ArrayList();
		list.add(n1);
		list.add(n2);
		list.add(n3);
		list.add(n4);
		list.add(n5);
		return list;
	}


	@Override
	public List<Attences> Weekjiatime1() {
		// TODO Auto-generated method stub
		String sql1 ="SELECT sum(overTime) as overTime from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+6)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and (attences.attFlag=3 or attences.attFlag=0  or attences.attFlag=1)";
		String sql2 ="SELECT sum(overTime) as overTime from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and (attences.attFlag=3 or attences.attFlag=0  or attences.attFlag=1)";
		String sql3 ="SELECT sum(overTime) as overTime from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and (attences.attFlag=3 or attences.attFlag=0  or attences.attFlag=1)";
		String sql4 ="SELECT sum(overTime) as overTime from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and (attences.attFlag=3 or attences.attFlag=0  or attences.attFlag=1)";
		String sql5 ="SELECT sum(overTime) as overTime from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+1)) and (attences.attFlag=3 or attences.attFlag=0  or attences.attFlag=1)";
		List<Attences> list1 =  DBUtil.exQuery(sql1,Attences.class);
		double n1 =list1.get(0).getOverTime();
		List<Attences> list2 =  DBUtil.exQuery(sql2,Attences.class);
		double n2 =list2.get(0).getOverTime();
		List<Attences> list3 =  DBUtil.exQuery(sql3,Attences.class);
		double n3 =list3.get(0).getOverTime();
		List<Attences> list4 =  DBUtil.exQuery(sql4,Attences.class);
		double n4 =list4.get(0).getOverTime();
		List<Attences> list5 =  DBUtil.exQuery(sql5,Attences.class);
		double n5 =list5.get(0).getOverTime();
		List list = new ArrayList();
		list.add(n1);
		list.add(n2);
		list.add(n3);
		list.add(n4);
		list.add(n5);
		return list;
	}


	@Override
	public List<Attences> Weekzhangchang1() {
		// TODO Auto-generated method stub
				String sql1 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+6)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attences.attFlag=0";
				String sql2 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+5)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attences.attFlag=0";
				String sql3 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+4)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attences.attFlag=0";
				String sql4 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+3)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attences.attFlag=0";
				String sql5 ="SELECT count(attId) as attId from attences where attTime>(SELECT  subdate(curdate(),date_format(curdate(),'%w')+2)) and attTime<(SELECT  subdate(curdate(),date_format(curdate(),'%w')+1)) and attences.attFlag=0";
				List<Attences> list1 =  DBUtil.exQuery(sql1,Attences.class);
				int n1 =list1.get(0).getAttId();
				List<Attences> list2 =  DBUtil.exQuery(sql2,Attences.class);
				int n2 =list2.get(0).getAttId();
				List<Attences> list3 =  DBUtil.exQuery(sql3,Attences.class);
				int n3 =list3.get(0).getAttId();
				List<Attences> list4 =  DBUtil.exQuery(sql4,Attences.class);
				int n4 =list4.get(0).getAttId();
				List<Attences> list5 =  DBUtil.exQuery(sql5,Attences.class);
				int n5 =list5.get(0).getAttId();
				List list = new ArrayList();
				list.add(n1);
				list.add(n2);
				list.add(n3);
				list.add(n4);
				list.add(n5);
				return list;
	}
	
	

	// 戴布斯：插入打卡记录用
	@Override
	public boolean insertClock(Attences attences) {
		// TODO Auto-generated method stub
		String sql = "insert into attences values (null,?,now(),?,?)";
		int i = DBUtil.exUpdate(sql, attences.getUserId(), attences.getAttFlag(), attences.getOverTime());
		return i > 0;
	}

	// 戴布斯：查询用户当日打卡记录次数
	@Override
	public int clockNum(int userId) {
		// TODO Auto-generated method stub
		/*
		 * yyyy表示年，MM表示月，dd表示天，hh表示用12小时制， HH表示用24小时制，mm表示分，ss表示秒，SSS表示毫米
		 */
		Date date = new Date();
		SimpleDateFormat h = new SimpleDateFormat("yyyy");// 年
		SimpleDateFormat m = new SimpleDateFormat("MM");// 月
		SimpleDateFormat d = new SimpleDateFormat("dd");// 日
		String hh = h.format(date);
		String mm = m.format(date);
		String today = h.format(date) + "-" + m.format(date) + "-" + d.format(date);
		String tomorrow = h.format(date) + "-" + m.format(date) + "-" + Integer.parseInt(d.format(date)) + 1;
		String sql = "SELECT count(attences.attId) as countClock from attences WHERE attTime >= ? and attTime < ? AND userId = ?";
		List list = DBUtil.exQuery(sql, CountClock.class, today, tomorrow, userId);
		CountClock countClock = (CountClock) list.get(0);
		System.out.println(countClock.getCountClock());
		return countClock.getCountClock();
	}

}
